﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using RBOSS.Data;
using System.Data;

namespace RBOSS.Business.ReportsModule
{
    public class Helper
    {
        public List<ReportsModule.Category> GetCategories()
        {
            using (RBOSSEntities re = new RBOSSEntities())
            {
                var query = (from c in re.Categories
                             select new Category { categoryId = c.CategoryId, categoryName = c.CategoryName });
                return query.ToList();
            }
        }   

        public DataTable getTopProducts(DateTime startDate, DateTime endDate, int CategorId)
        {
            RBOSSEntities re = new RBOSSEntities();
            var query = (from o in re.Orders
                         join op in re.OrderDetails on o.OrderId equals op.OrderId
                         join p in re.Products on op.ProductId equals p.ProductId
                         where o.OrderDate >= startDate.Date && o.OrderDate <= endDate.Date
                         group op by p into productsReport
                         orderby productsReport.Key.Quantity descending
                         select new
                         {
                             CategoryID = productsReport.Key.CategoryId,
                             ProductID = productsReport.Key.ProductId,
                             Quantity = productsReport.Sum(op => op.Quantity),
                             Revenue = productsReport.Sum(op => op.Quantity * op.Price),
                             ProductName = productsReport.Key.ProductName
                         } into finalReport
                         orderby finalReport.Quantity descending
                         select finalReport);

            if (CategorId != -1)
                query = query.Where(q => q.CategoryID == CategorId).OrderByDescending(q => q.Quantity);

            DataTable dtProducts = new DataTable();
            dtProducts.Columns.Add("ProductID");
            dtProducts.Columns.Add("ProductName");
            dtProducts.Columns.Add("Quantity", System.Type.GetType("System.Int32"));
            dtProducts.Columns.Add("Amount", System.Type.GetType("System.Decimal"));

            foreach (var v in query)
            {
                DataRow dr = dtProducts.NewRow();
                dr[0] = v.ProductID;
                dr[1] = v.ProductName;
                dr[2] = v.Quantity;
                dr[3] = v.Revenue.ToString("#.##");

                dtProducts.Rows.Add(dr);
            }

            return dtProducts;
        }

        public DataTable getLeastProducts(DateTime startDate, DateTime endDate, int CategorId)
        {
            RBOSSEntities re = new RBOSSEntities();
            var query = (from o in re.Orders
                         join op in re.OrderDetails on o.OrderId equals op.OrderId
                         join p in re.Products on op.ProductId equals p.ProductId
                         where o.OrderDate >= startDate.Date && o.OrderDate <= endDate.Date
                         group op by p into productsReport
                         orderby productsReport.Key.Quantity ascending
                         select new
                         {
                             CategoryID = productsReport.Key.CategoryId,
                             ProductID = productsReport.Key.ProductId,
                             Quantity = productsReport.Sum(op => op.Quantity),
                             Revenue = productsReport.Sum(op => op.Quantity * op.Price),
                             ProductName = productsReport.Key.ProductName
                         } into finalReport 
                         orderby finalReport.Quantity ascending
                         select finalReport);

            if (CategorId != -1)
                query = query.Where(q => q.CategoryID == CategorId).OrderBy(q => q.Quantity);

            DataTable dtProducts = new DataTable();
            dtProducts.Columns.Add("ProductID");
            dtProducts.Columns.Add("ProductName");
            dtProducts.Columns.Add("Quantity", System.Type.GetType("System.Int32"));
            dtProducts.Columns.Add("Amount", System.Type.GetType("System.Decimal"));

            foreach (var v in query)
            {
                DataRow dr = dtProducts.NewRow();
                dr[0] = v.ProductID;
                dr[1] = v.ProductName;
                dr[2] = v.Quantity;
                dr[3] = v.Revenue.ToString("#.##");

                dtProducts.Rows.Add(dr);
            }

            return dtProducts;
        }

        public DataTable getTopProfitByVendors(DateTime startDate, DateTime endDate)
        {
            RBOSSEntities re = new RBOSSEntities();
            MyFinanceDBEntities me = new MyFinanceDBEntities();

            var query1 = (from o in re.Orders
                          where o.OrderDate >= startDate.Date && o.OrderDate <= endDate.Date
                          select new { o.OrderId }).ToArray();

            var query2 = (from o in query1
                          join bt in me.BankTransactions on o.OrderId equals bt.OrderID
                          select new
                          {
                              bt.OrderID,
                              bt.VendorID,
                              bt.Percentage,
                              bt.TotalAmount
                          }).ToArray();

            var query3 = (from o in query2
                          join u in re.Users on o.VendorID equals u.UserId
                          group new { o, u } by new { u.FirstName, u.LastName } into vendorsReport
                          select new
                          {
                              VendorName = vendorsReport.Key.FirstName + " " + vendorsReport.Key.LastName,
                              VendorAmount = vendorsReport.Sum(o => o.o.TotalAmount),
                              CompanyAmount = vendorsReport.Sum(o => o.o.TotalAmount - (o.o.TotalAmount * (o.o.Percentage / 100))),
                          } into finalReport
                          orderby finalReport.CompanyAmount descending
                          select finalReport);

            DataTable dtVendors = new DataTable();
            dtVendors.Columns.Add("VendorName");
            dtVendors.Columns.Add("VendorAmount", System.Type.GetType("System.Decimal"));
            dtVendors.Columns.Add("CompanyAmount", System.Type.GetType("System.Decimal"));

            foreach (var v in query3)
            {
                DataRow dr = dtVendors.NewRow();
                dr[0] = v.VendorName;
                dr[1] = v.VendorAmount;
                dr[2] = v.CompanyAmount;

                dtVendors.Rows.Add(dr);
            }

            return dtVendors;
        }

        public DataTable getLeastProfitByVendors(DateTime startDate, DateTime endDate)
        {
            RBOSSEntities re = new RBOSSEntities();
            MyFinanceDBEntities me = new MyFinanceDBEntities();

            var query1 = (from o in re.Orders
                          where o.OrderDate >= startDate.Date && o.OrderDate <= endDate.Date
                          select new {o.OrderId}).ToArray();

            var query2 = (from o in query1
                          join bt in me.BankTransactions on o.OrderId equals bt.OrderID
                          select new
                          {
                              bt.OrderID,
                              bt.VendorID,
                              bt.Percentage,
                              bt.TotalAmount
                          }).ToArray();

            var query3 = (from o in query2
                          join u in re.Users on o.VendorID equals u.UserId
                          group new { o, u } by new { u.FirstName, u.LastName } into vendorsReport
                          select new
                          {
                              VendorName = vendorsReport.Key.FirstName + " " + vendorsReport.Key.LastName,
                              VendorAmount = vendorsReport.Sum(o => o.o.TotalAmount),
                              CompanyAmount = vendorsReport.Sum(o => o.o.TotalAmount - (o.o.TotalAmount * (o.o.Percentage / 100))),
                          } into finalReport
                          orderby finalReport.CompanyAmount ascending
                          select finalReport);

            DataTable dtVendors = new DataTable();
            dtVendors.Columns.Add("VendorName");
            dtVendors.Columns.Add("VendorAmount", System.Type.GetType("System.Decimal"));
            dtVendors.Columns.Add("CompanyAmount", System.Type.GetType("System.Decimal"));

            foreach (var v in query3)
            {
                DataRow dr = dtVendors.NewRow();
                dr[0] = v.VendorName;
                dr[1] = v.VendorAmount;
                dr[2] = v.CompanyAmount;

                dtVendors.Rows.Add(dr);
            }

            return dtVendors;
        }

        public DataTable GetInvoiceReceipt(int OrderID)
        {
            RBOSSEntities re = new RBOSSEntities();
            var query = (from o in re.Orders
                         join op in re.OrderDetails on o.OrderId equals op.OrderId
                         join p in re.Products on op.ProductId equals p.ProductId
                         join u in re.Users on o.UserId equals u.UserId
                         where o.OrderId == OrderID
                         select new
                         {
                             OrderId = o.OrderId,
                             UserId = u.UserId,
                             CustomerName = u.FirstName + " " + u.LastName,
                             OrderDate = o.OrderDate,
                             ProductName = p.ProductName,
                             Quantity = op.Quantity,
                             Price = op.Price,
                             Total = op.Quantity * op.Price
                         } into finalReport
                         orderby finalReport.ProductName ascending
                         select finalReport);

            DataTable dtProducts = new DataTable();
            dtProducts.Columns.Add("OrderId");
            dtProducts.Columns.Add("UserId");
            dtProducts.Columns.Add("CustomerName");
            dtProducts.Columns.Add("OrderDate");
            dtProducts.Columns.Add("ProductName");
            dtProducts.Columns.Add("Quantity");
            dtProducts.Columns.Add("Price");
            dtProducts.Columns.Add("Total", System.Type.GetType("System.Decimal"));
            dtProducts.Columns.Add("SerialNO");

            int rank = 1;
            foreach (var v in query)
            {
                DataRow dr = dtProducts.NewRow();
                dr[0] = v.OrderId;
                dr[1] = v.UserId;
                dr[2] = v.CustomerName;
                dr[3] = v.OrderDate;
                dr[4] = v.ProductName;
                dr[5] = v.Quantity;
                dr[6] = v.Price;
                dr[7] = v.Total;
                dr[8] = rank;

                dtProducts.Rows.Add(dr);
                rank++;
            }

            return dtProducts;
        }
    }
}
